﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using SpreadsheetGear;
using Sustainalytics.DataLayer.ConnectionStrings;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.Entities;
using Sustainalytics.Entities.ConnectionStrings;

namespace Sustainalytics.HistoricalData.Importer
{
    public class Program
    {
        private const int companyCapitalIQIDColumnIndex = 0;
        private const int referenceDateColumnIndex = 1;
        private const int rawScoresSheetIndex = 0;
        private const int companyIndicatorStartColumnIndex = 2;
        private const int companyIndicatorEndColumnIndex = 149;
        public static void Main()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ClientPlatform"].ConnectionString;
            var connectionStringsManager =
                ConnectionStringsManager.GetInstance(ConnectionStringsRepository.GetInstance(connectionString));
            var repHistData = new RepositoryCollection<CompanyHistoricalDataIndicators>(connectionStringsManager.GetConnectionString(ConnectionStringType.DWArchive),"CompanyHistoricalDataIndicators_live");

            var filePath = @"..\..\Adjustments\Copy of Copy of HD Companies with scores over 95.csv";
            var relativeDataReport = Factory.GetWorkbookSet().Workbooks.Open(filePath);

            var rawScoresSheet = relativeDataReport.Worksheets[rawScoresSheetIndex];

            var rowCount = rawScoresSheet.UsedRange.RowCount;

            // We could use foreach (IRange cell in usedRange) for cleaner
            // code, but this is faster.

            Dictionary<int,string> indicatorCodesDictionary=new Dictionary<int, string>();

            for (var column = companyIndicatorStartColumnIndex; column <= companyIndicatorEndColumnIndex; column++)
            {
                var code = rawScoresSheet.Cells[0, column].Value.ToString();
                if (string.IsNullOrEmpty(code))
                    throw new ArgumentOutOfRangeException();
                indicatorCodesDictionary.Add(column,code);
            }


            for (var row = 1; row < rowCount; row++)
            {
                string cIQID = rawScoresSheet.Cells[row, companyCapitalIQIDColumnIndex].Value.ToString();

                DateTime beginningOfMonthDate=DateTime.FromOADate(double.Parse(rawScoresSheet.Cells[row, referenceDateColumnIndex].Value.ToString())).ToLocalTime();

                var historicalData =
                    repHistData.ReadWhere(x => x.CapitalIQID == cIQID && x.ProcessedDate == beginningOfMonthDate, 0, 1)
                        .FirstOrDefault();
                if (historicalData == null)
                    continue;

                for (var column = companyIndicatorStartColumnIndex; column <= companyIndicatorEndColumnIndex; column++)
                {
                    string code;
                    int rawScore;

                    var strValue = rawScoresSheet.Cells[row, column].Value.ToString();
                    if (strValue=="N/A")
                        continue;

                    if (!int.TryParse(strValue, out rawScore))
                        continue;

                    if (!indicatorCodesDictionary.TryGetValue(column, out code))
                        throw new ArgumentOutOfRangeException("column");

                   var index= historicalData.Indicators.FindIndex(x => string.Equals(x.IndicatorNumber, code, StringComparison.InvariantCultureIgnoreCase));
                    if (index != -1)
                    {
                        historicalData.Indicators[index].RawScore = rawScore;
                    }

                }
                repHistData.Update(historicalData.Id, historicalData);
            }
        }
    }
}
